package com.ssy.dao;

import com.ssy.common.PageInfo;
import com.ssy.entity.Class;
import com.ssy.vo.ClassVo;
import com.ssy.vo.StudentVo;

import java.util.HashMap;
import java.util.Map;

/**
 * ClassDao
 *
 * @author ssy
 * @date 2020/11/18
 **/
public class ClassDao extends BaseDao {

    public PageInfo<ClassVo> selectPage(Map<String, Object> params, Integer pageNo, Integer limitNo) {
        String sql = "SELECT c_id AS cId,number AS number,cname AS cname,m.m_id AS mId,m.majorName AS majorName,SUM AS sum,\n" +
                "inTime AS inTime,instructor AS instructor FROM class c INNER JOIN major m ON c.m_id=m.m_id where";
        if (params.containsKey("number")) {
            sql = sql + " number like '%" + params.get("number") + "%'  and";
        }
        if (params.containsKey("cname")) {
            sql = sql + " cname like '%" + params.get("cname") + "%'  and";
        }
        if (params.containsKey("majorId")) {
            sql = sql + " m.m_id = " + params.get("majorId") + "  and";
        }
        if (params.containsKey("inTime")) {
            sql = sql + " inTime = '" + params.get("inTime") + "'  and";

        }
        if (params.containsKey("instructor")) {
            sql = sql + " instructor like '%" + params.get("instructor") + "%'  and";
        }
        if (sql.endsWith("and")) {
            sql = sql.substring(0, sql.lastIndexOf("and"));
        }
        if (sql.endsWith("where")) {
            sql = sql.substring(0, sql.lastIndexOf("where"));
        }
        return this.selectPage(sql, ClassVo.class, pageNo, limitNo);
    }

    public boolean insertClass(String number, String cname, int majorId, String sum, String inTime, String instructor) {
        String sql = "INSERT INTO class(number,cname,m_id,sum,inTime,instructor) VALUE(?,?,?,?,?,?)";
        int cId = this.insert(sql, number, cname, majorId, sum, inTime, instructor);
        return cId > 0 ? true : false;
    }

    public boolean modifyClass(String number, String cname, int majorId, String sum, String inTime, String instructor, int cId) {
        String sql = "UPDATE class SET number=?,cname=?,m_id=?,sum=?,inTime=?,instructor=? WHERE c_id=?";
        return this.update(sql, number, cname, majorId, sum, inTime, instructor, cId);
    }

    public boolean batchDeleteClasss(String[] ids) {
        String sql = "delete from class where c_id=?";
        return this.batchDelete(sql, ids);
    }

    public boolean deleteClasss(int id) {
        String sql = "delete from class where c_id=?";
        return this.update(sql, id);
    }

    public PageInfo<StudentVo> selectStudentPage(HashMap<String, Object> param, Integer pageNo, Integer limitNo) {
        String sql = "SELECT s.number AS number,s.name AS sname,sex AS sex,birthday AS birthday,address AS address,\n" +
                "phone AS phone,remarks AS remarks,m.majorName AS major,c.cname AS className\n" +
                "FROM student s INNER JOIN major m ON s.m_id=m.m_id INNER JOIN class c ON s.s_class=c.c_id WHERE ";
        if (param.containsKey("cId")) {
            sql = sql + " c.c_id = " + param.get("cId") + "  and";
        }
        if (sql.endsWith("and")) {
            sql = sql.substring(0, sql.lastIndexOf("and"));
        }
        if (sql.endsWith("where")) {
            sql = sql.substring(0, sql.lastIndexOf("where"));
        }
        return this.selectPage(sql, StudentVo.class, pageNo, limitNo);
    }

    public Class selectClassByNumber(String number) {
        String sql = "SELECT c_id AS cId FROM class WHERE number=?";
        return this.selectOne(sql, Class.class, Integer.parseInt(number));
    }

    public Class selectClassByCname(String cname) {
        String sql = "SELECT c_id AS cId FROM class WHERE cname=?";
        return this.selectOne(sql, Class.class, cname);
    }

    public Class selectClassById(String cId) {
        String sql = "SELECT number AS number,cname AS cname FROM class WHERE c_id=?";
        return this.selectOne(sql, Class.class, Integer.parseInt(cId));
    }
}
